using System;

public class SanGDVL
{
    private Common comm = new Common();

    public bool ChapNhanHoSo(string ma_san, string ma_ntv, string ma_vlt)
    {
        try
        {
            bool flag = false;
            if (this.comm.execNoneQuery("Update  SanGiaoDich_nop_ho_so set duyet = 1 where ma_san ='" + ma_san + "' and ma_ntv ='" + ma_ntv + "' and ma_vlt ='" + ma_vlt + "'") > 0)
            {
                flag = true;
            }
            return flag;
        }
        catch (Exception)
        {
            return false;
        }
    }

    public string getMaNguoi(string username)
    {
        return this.comm.getDataTable("select ma_nguoi from ConNguoi_nguoi where tai_khoan = '" + username + "' ").Rows[0][0].ToString();
    }

    public bool GoiHoSo(string ma_san, string ma_ntv, string ma_vlt, int type)
    {
        try
        {
            bool flag = false;
            if (this.comm.execNoneQuery(string.Concat(new object[] { "INSERT INTO SanGiaoDich_nop_ho_so values ('", ma_san, "','", ma_ntv, "','", ma_vlt, "',", type, ",0)" })) > 0)
            {
                flag = true;
            }
            return flag;
        }
        catch (Exception)
        {
            return false;
        }
    }

    public bool KetquaSan(string ma_san, string ma_vlt, string ma_ntv)
    {
        return (this.comm.numOfRecord("select * from SanGiaoDich_ket_qua_phong_van where ma_san = '" + ma_san + "' and ma_vlt= '" + ma_vlt + "' and ma_ntv = '" + ma_ntv + "'") > 0);
    }

    public bool LuuHoSo(string ma_san, string ma_ntv, string ma_vlt)
    {
        try
        {
            bool flag = false;
            if (this.comm.execNoneQuery("INSERT INTO SanGiaoDich_ntv_luu_hoso values('" + ma_san + "','" + ma_ntv + "','" + ma_vlt + "')") > 0)
            {
                flag = true;
            }
            return flag;
        }
        catch (Exception)
        {
            return false;
        }
    }

    public bool LuuUngVien(string ma_san, string ma_ntv, string ma_ntd)
    {
        try
        {
            bool flag = false;
            if (this.comm.execNoneQuery("INSERT INTO SanGiaoDich_luu_hoso values('" + ma_san + "','" + ma_ntd + "','" + ma_ntv + "')") > 0)
            {
                flag = true;
            }
            return flag;
        }
        catch (Exception)
        {
            return false;
        }
    }

    public bool NhapKetQuaTuyenDung(string ma_san, string ma_vlt, string ma_ntv, string ma_ntd, string date, string mo_ta, string ketqua)
    {
        bool flag2;
        bool flag = false;
        try
        {
            if (this.comm.execNoneQuery("INSERT INTO [SanGiaoDich_ket_qua_phong_van]  VALUES('" + ma_san + "','" + ma_ntv + "','" + ma_vlt + "','" + ma_ntd + "'," + ketqua + ",'" + date + "',N'" + mo_ta + "')") > 0)
            {
                flag = true;
            }
            flag2 = flag;
        }
        catch (Exception)
        {
            throw;
        }
        return flag2;
    }

    public string ntd_soNTVDaGoi(string ma_san, string ma_ntd)
    {
        return this.comm.execScalar("select count(*) from sangiaodich_nop_ho_so where ma_san = '" + ma_san + "' and ma_vlt  in (select ma_ho_so_tuyen_dung from SanGiaoDich_doanh_nghiep where ma_doanh_nghiep = '" + ma_ntd + "') and kieu_nop = 1");
    }

    public string ntd_soNTVDaLuu(string ma_san, string ma_ntd)
    {
        return this.comm.execScalar("select count(*) from sangiaodich_luu_hoso where ma_san = '" + ma_san + "' and ma_ntd = '" + ma_ntd + "'");
    }

    public string ntd_soNTVDaNop(string ma_san, string ma_ntd)
    {
        return this.comm.execScalar("select count(*) from sangiaodich_nop_ho_so where ma_san = '" + ma_san + "' and ma_vlt  in (select ma_ho_so_tuyen_dung from SanGiaoDich_doanh_nghiep where ma_doanh_nghiep = '" + ma_ntd + "') and kieu_nop = 0");
    }

    public string ntd_soNTVPV(string ma_san, string ma_ntd)
    {
        return this.comm.execScalar("select count(*) from sangiaodich_nop_ho_so where ma_san = '" + ma_san + "' and ma_vlt  in (select ma_ho_so_tuyen_dung from SanGiaoDich_doanh_nghiep where ma_doanh_nghiep = '" + ma_ntd + "') and duyet = 1");
    }

    public string SoGoiPV(string ma_san, string maNTV)
    {
        return this.comm.execScalar("select count(*) from sangiaodich_nop_ho_so where ma_san = '" + ma_san + "' and ma_ntv = '" + maNTV + "' and duyet = 1");
    }

    public string SoLuongMoi(string ma_san)
    {
        return this.comm.execScalar("select count(*) from SanGiaoDich_nop_ho_so where duyet = 1 and ma_san =  '" + ma_san + "'");
    }

    public string SoLuongNop(string ma_san)
    {
        return this.comm.execScalar("select count(*) from SanGiaoDich_nop_ho_so where ma_san =  '" + ma_san + "'");
    }

    public string SoLuongNTD(string ma_san)
    {
        return this.comm.execScalar("select count(A.ma_doanh_nghiep) from (select ma_doanh_nghiep from SanGiaoDich_doanh_nghiep where ma_san =  '" + ma_san + "' group by ma_doanh_nghiep) as A");
    }

    public string SoLuongNTV(string ma_san)
    {
        return this.comm.execScalar("select count(*) from SanGiaoDich_nguoi_tim_viec where ma_san =  '" + ma_san + "'");
    }

    public string SoLuongVLT(string ma_san)
    {
        return this.comm.execScalar("select count(*) from SanGiaoDich_doanh_nghiep where ma_san =  '" + ma_san + "'");
    }

    public string SoNTDDaMoi(string ma_san, string maNTV)
    {
        return this.comm.execScalar("select count(*) from sangiaodich_nop_ho_so where ma_san = '" + ma_san + "' and ma_ntv = '" + maNTV + "' and kieu_nop = 1 ");
    }

    public string SoNTVDaNop(string ma_san, string maNTV)
    {
        return this.comm.execScalar("select count(*) from sangiaodich_nop_ho_so where ma_san = '" + ma_san + "' and ma_ntv = '" + maNTV + "' and kieu_nop = 0");
    }

    public string SoVLTDaLuu(string ma_san, string maNTV)
    {
        return this.comm.execScalar("select count(*) from sangiaodich_ntv_luu_hoso where ma_san = '" + ma_san + "' and ma_ntv = '" + maNTV + "'");
    }

    public string SoVLTDaNop(string ma_san, string maNTV)
    {
        return this.comm.execScalar("select count(*) from sangiaodich_nop_ho_so where ma_san = '" + ma_san + "' and ma_ntv = '" + maNTV + "' and kieu_nop = 0");
    }

    public bool SuaKetQuaTuyenDung(string ma_san, string ma_vlt, string ma_ntv, string ma_ntd, string date, string mo_ta, string ketqua)
    {
        bool flag2;
        bool flag = false;
        try
        {
            if (this.comm.execNoneQuery("UPDATE [SanGiaoDich_ket_qua_phong_van] SET Ket_qua = " + ketqua + " , ngay_phong_van = '" + date + "' , ghi_chu = N'" + mo_ta + "' WHERE ma_san='" + ma_san + "' and ma_ntv='" + ma_ntv + "' and ma_vlt = '" + ma_vlt + "'") > 0)
            {
                flag = true;
            }
            flag2 = flag;
        }
        catch (Exception)
        {
            throw;
        }
        return flag2;
    }

    public bool XoaHoSoGoi(string ma_san, string ma_ntv, string ma_vlt)
    {
        try
        {
            bool flag = false;
            if (this.comm.execNoneQuery("Delete from SanGiaoDich_nop_ho_so where ma_san ='" + ma_san + "' and ma_ntv ='" + ma_ntv + "' and ma_vlt ='" + ma_vlt + "'") > 0)
            {
                flag = true;
            }
            return flag;
        }
        catch (Exception)
        {
            return false;
        }
    }

    public bool XoaHoSoLuu(string ma_san, string ma_ntv, string ma_vlt)
    {
        try
        {
            bool flag = false;
            if (this.comm.execNoneQuery("Delete from SanGiaoDich_ntv_luu_hoso where ma_san ='" + ma_san + "' and ma_ntv ='" + ma_ntv + "' and ma_hoso ='" + ma_vlt + "'") > 0)
            {
                flag = true;
            }
            return flag;
        }
        catch (Exception)
        {
            return false;
        }
    }

    public bool XoaNTVLuu(string ma_san, string ma_ntd, string ma_ntv)
    {
        try
        {
            bool flag = false;
            if (this.comm.execNoneQuery("Delete from SanGiaoDich_luu_hoso where ma_san ='" + ma_san + "' and ma_ntv ='" + ma_ntv + "' and ma_ntd ='" + ma_ntd + "'" + "") > 0)
            {
                flag = true;
            }
            return flag;
        }
        catch (Exception)
        {
            return false;
        }
    }
}

